﻿Imports System.Data.OleDb
Imports System.Text
Imports System.Reflection
Imports System.Collections.Specialized

Public Class EnglishWordForm

    Private _ParamsValues As New Hashtable
    Private Sub MyButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
        Try
            _ParamsValues.Clear()

            '先做画面的CHECK
            If (Not (DoCheck())) Then
                Exit Sub
            End If

            If rad正常.Checked Then
                ReportConst.TypeReport = 0
            ElseIf rad单词模式.Checked Then
                ReportConst.TypeReport = 1
            ElseIf rad意思模式.Checked Then
                ReportConst.TypeReport = 2
            End If

            If Me.CheckBox1.Checked Then
                If rad多到少.Checked Then
                    ReportConst.EnglishOrderBy = 1
                Else
                    ReportConst.EnglishOrderBy = 0
                End If
            Else
                ReportConst.EnglishOrderBy = 2
            End If

            If rad按时间范围.Checked Then
                ReportConst.EnglishTypeReport = 0
                _ParamsValues.Add("开始时间", Me.dtStartTime.Text.ToString())
                _ParamsValues.Add("结束时间", Me.dtEndTime.Text.ToString())
            ElseIf rad按备注.Checked Then
                ReportConst.EnglishTypeReport = 2
                _ParamsValues.Add("当前英语备注ID", 当前英语备注ID.ToString)
            ElseIf rad按打印记录.Checked Then
                ReportConst.EnglishTypeReport = 3
                Dim dt As String = Convert.ToDateTime(cbo报表打印时间.Text).ToString("yyyy-MM-dd HH:mm:ss")
                Dim strSql As String = "select * from printLog where [type]=0 and datetime([datetime])='" + dt + "'"
                Dim rs As ADODB.Recordset = ct.GetTable(strSql)
                Dim ids As String = ""
                If rs IsNot Nothing Then
                    If rs.EOF Then
                        rs.Close()
                        Return
                    Else
                        ids = CStr(rs.Fields("keys").Value.ToString())
                        rs.Close()
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(rs) '20100531
                        rs = Nothing
                    End If
                End If
                _ParamsValues.Add("编号列表", ids)
            Else
                ReportConst.EnglishTypeReport = 1
            End If
            _ParamsValues.Add("RowCount", Me.NumericUpDown2.Value)

            Dim englishFolder As String = System.IO.Path.Combine(GetCurrentDirectory, "Report")
            If Not System.IO.Directory.Exists(englishFolder) Then
                System.IO.Directory.CreateDirectory(englishFolder)
            End If

            Dim englishPath As String = System.IO.Path.Combine(englishFolder, "English.xls")
            Dim bCreate As Boolean = True
            If System.IO.File.Exists(englishPath) Then
                'Try
                System.IO.File.Delete(englishPath)
                'Catch ex As Exception
                '    bCreate = False
                'End Try

            End If

            If bCreate Then
                Dim dt As System.Data.DataTable = CreateDataSource()
                If dt.Rows.Count = 0 Then
                    ShowMessageBox("没有数据")
                    Exit Sub
                End If

                'If rad单词模式.Checked Then
                '    For index As Integer = 0 To dt.Rows.Count - 1
                '        dt.Rows(index)("WORD") = ""
                '    Next
                'ElseIf rad意思模式.Checked Then
                '    For index As Integer = 0 To dt.Rows.Count - 1
                '        dt.Rows(index)("CHINESEWORD") = ""
                '    Next
                'End If

                Dim englishCreate As New EnglishExcelCreate(englishPath)
                englishCreate.报表记录条数字 = Me.报表记录条数字
                englishCreate.报表字体 = Me.报表字体

                AddHandler englishCreate.PrintProcess, AddressOf PrintProcess
                englishCreate.DoOutData(dt)
                RemoveHandler englishCreate.PrintProcess, AddressOf PrintProcess

                If chk保存打印数据到数据库.Checked Then
                    保存数据(dt)
                End If

                PrintProcess("正在打开文件......")
                Using pro As New Process
                    pro.StartInfo.FileName = englishPath
                    pro.Start()
                End Using
                PrintProcess("")
            End If
            'ShowMessageBox("报表生成成功")
        Catch ex As Exception
            ShowMessageBox(ex.Message)
            UTIL.CException.SaveExcepiton(UTIL.ErrorType.Error, ex, MethodBase.GetCurrentMethod().GetType()) 'A-2009-10-11 
        End Try

    End Sub

#Region "关闭窗体"
    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub
#End Region

#Region "画面的CHECK"
    Public Function DoCheck() As Boolean
        If rad按时间范围.Checked Then
            Dim strTimeStart As String = ""
            Dim strTimeEnd As String = ""
            strTimeStart = String.Format("{0:00}{1:00}{2:00}", dtStartTime.Value.Year, dtStartTime.Value.Month, dtStartTime.Value.Day)
            strTimeEnd = String.Format("{0:00}{1:00}{2:00}", dtEndTime.Value.Year, dtEndTime.Value.Month, dtEndTime.Value.Day)

            If strTimeStart > strTimeEnd Then
                ShowMessageBox("开始时间不能大于结束时间")
                Return False
            End If
        ElseIf rad按打印记录.Checked Then
            If cbo报表打印时间.Text.Trim = "" Then
                ShowMessageBox("没有打印记录")
                Return False
            End If
        End If

        Return True
    End Function
#End Region

    Dim itemMenu As System.Windows.Forms.ToolStripMenuItem
    Private Sub EnglishWordForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim rs As ADODB.Recordset
        Dim strSql As String = "select * from printLog where [type]=0 order by datetime desc"
        rs = ct.GetTable(strSql)
        If rs IsNot Nothing Then
            While Not rs.EOF
                cbo报表打印时间.Items.Add(rs.Fields("datetime").Value.ToString())
                rs.MoveNext()
            End While
            rs.Close()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(rs)
            rs = Nothing
        End If

        If cbo报表打印时间.Items.Count > 0 Then
            cbo报表打印时间.SelectedIndex = 0
        End If

    End Sub

    Private Sub rad按时间范围_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rad按记录数.CheckedChanged, rad按时间范围.CheckedChanged, rad按打印记录.CheckedChanged, rad按备注.CheckedChanged
        If rad按时间范围.Checked Then
            gp时间范围.Enabled = True
            gp打印记录.Enabled = False
            gp备注.Enabled = False
        End If

        If rad按备注.Checked Then
            gp时间范围.Enabled = False
            gp打印记录.Enabled = False
            gp备注.Enabled = True
        End If

        If rad按记录数.Checked Then
            gp时间范围.Enabled = False
            gp打印记录.Enabled = False
            gp备注.Enabled = False
        End If

        If rad按打印记录.Checked Then
            gp时间范围.Enabled = False
            gp打印记录.Enabled = True
            gp备注.Enabled = False
        End If
    End Sub

    Private Sub CheckBox1_CheckStateChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckStateChanged
        If CheckBox1.Checked Then
            rad多到少.Enabled = True
            rad少到多.Enabled = True
        Else
            rad多到少.Enabled = False
            rad少到多.Enabled = False
        End If
    End Sub

    Dim 当前英语备注ID As Integer = 0
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Using frm As wys.Control.Vb.ListForm = New wys.Control.Vb.ListForm
            frm.strName = Me.strName
            frm.iType = Me.iType
            frm.SQL = "select * from MEMO where type=1"
            Dim 显示列 As New StringCollection
            显示列.Add("TITLE")
            frm.显示列 = 显示列
            frm.ShowDialog()
            If frm.返回值.Count > 0 Then
                当前英语备注ID = CInt(frm.返回值(0))
                txt备注.Text = CStr(frm.返回值(1))
            Else
                当前英语备注ID = 0
                txt备注.Text = ""
            End If
        End Using
    End Sub

    Private Sub cbo报表打印时间_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbo报表打印时间.SelectedIndexChanged
        Dim dt As String = Convert.ToDateTime(cbo报表打印时间.Text).ToString("yyyy-MM-dd HH:mm:ss")
        Dim strSql As String = "select * from printLog where [type]=0 and datetime([datetime])='" + dt + "'"
        Dim rs As ADODB.Recordset = ct.GetTable(strSql)
        Dim ids As String = ""
        If rs IsNot Nothing Then
            If rs.EOF Then
                rs.Close()
                System.Runtime.InteropServices.Marshal.ReleaseComObject(rs) '20100531
                Return
            Else
                ids = CStr(rs.Fields("keys").Value.ToString())
                rs.Close()
                System.Runtime.InteropServices.Marshal.ReleaseComObject(rs) '20100531
                rs = Nothing
            End If
        End If
        If ids <> "" Then
            strSql = "select id,WORD from XSCENLISHWORD where id in(" + ids + " ) "
            Dim strSql1 As String = "select count(*) as PageCount from XSCENLISHWORD where id in( " + ids + " ) "
            rs = ct.GetTable(strSql1)
            If rs IsNot Nothing Then
                If rs.EOF Then
                    rs.Close()
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(rs) '20100531
                    Return
                Else
                    NumericUpDown2.Value = CInt(rs.Fields("PageCount").Value)
                    rs.Close()
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(rs) '20100531
                End If
            End If
        End If
    End Sub

#Region "窗体数据保存的重载方法 A-2010-09-06"

    Public Overrides Sub 保存窗体数据到数据库(ByRef dtData As System.Data.DataTable)
        If rad按记录数.Checked Then
            dtData.Rows.Add(New Object() {"类型", "", "正常"})
        ElseIf rad按时间范围.Checked Then
            dtData.Rows.Add(New Object() {"类型", "", "按时间范围"})
        ElseIf rad按备注.Checked Then
            dtData.Rows.Add(New Object() {"类型", "", "按备注"})
        ElseIf rad按打印记录.Checked Then
            dtData.Rows.Add(New Object() {"类型", "", "按打印记录"})
        End If

        '时间范围
        dtData.Rows.Add(New Object() {"时间范围.开始.年", "", dtStartTime.Value.Year.ToString})
        dtData.Rows.Add(New Object() {"时间范围.开始.月", "", dtStartTime.Value.Month.ToString})
        dtData.Rows.Add(New Object() {"时间范围.开始.日", "", dtStartTime.Value.Day.ToString})

        dtData.Rows.Add(New Object() {"时间范围.结束.年", "", dtEndTime.Value.Year.ToString})
        dtData.Rows.Add(New Object() {"时间范围.结束.月", "", dtEndTime.Value.Month.ToString})
        dtData.Rows.Add(New Object() {"时间范围.结束.日", "", dtEndTime.Value.Day.ToString})

        '备注
        dtData.Rows.Add(New Object() {"备注.内容", "", txt备注.Text})
        dtData.Rows.Add(New Object() {"备注.编号", "", 当前英语备注ID.ToString})
        '打印记录
        '浏览条件
        If CheckBox1.Checked Then
            dtData.Rows.Add(New Object() {"浏览次数是否选中", "", "选中"})
        Else
            dtData.Rows.Add(New Object() {"浏览次数是否选中", "", "不选中"})
        End If
        If rad多到少.Checked Then
            dtData.Rows.Add(New Object() {"排序", "", "多到少"})
        Else
            dtData.Rows.Add(New Object() {"排序", "", "少到多"})
        End If
        '模式
        If rad正常.Checked Then
            dtData.Rows.Add(New Object() {"模式", "", "正常"})
        ElseIf rad单词模式.Checked Then
            dtData.Rows.Add(New Object() {"模式", "", "单词模式"})
        ElseIf rad意思模式.Checked Then
            dtData.Rows.Add(New Object() {"模式", "", "意思模式"})
        End If
        '记录数
        dtData.Rows.Add(New Object() {"记录数", "", NumericUpDown2.Value.ToString})
    End Sub

    Public Overrides Sub 存取数据库数据到窗体(ByVal dtDbData As System.Data.DataTable)

    End Sub

#Region "创建英语数据源"
    Private Function CreateDataSource() As DataTable
        Dim ds As New DataSet

        CreateDataSource = Nothing
        '声明适配器
        Dim oa As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
        '声明表当数据源
        Dim dt As New DataTable("XSCENLISHWORD")
        '连接查询数据
        Dim sb As New StringBuilder

        '新版
        If ReportConst.TypeReport = 0 Then
            sb.AppendLine(" SELECT WORD,CHINESEWORD,Count")
            sb.AppendLine(",id ")
        ElseIf ReportConst.TypeReport = 1 Then
            sb.AppendLine(" SELECT ")
            sb.AppendLine(" WORD ")
            sb.AppendLine(" ,''as CHINESEWORD ")
            sb.AppendLine(",id ")
        ElseIf ReportConst.TypeReport = 2 Then
            sb.AppendLine(" SELECT ")
            sb.AppendLine(" ''as WORD ")
            sb.AppendLine(" , CHINESEWORD ")
            sb.AppendLine(",id ")
        End If
        sb.AppendLine(" from XSCENLISHWORD")
        sb.AppendLine(" WHERE 1=1 ")
        '时间范围
        If ReportConst.EnglishTypeReport = 0 Then
            sb.AppendLine(" AND date([OPTIME])>='" + DateTime.Parse(_ParamsValues.Item("开始时间")).ToString("yyyy-MM-dd") + "'")
            sb.AppendLine(" AND date([OPTIME])<='" + DateTime.Parse(_ParamsValues.Item("结束时间")).ToString("yyyy-MM-dd") + "'")
            '备注
        ElseIf ReportConst.EnglishTypeReport = 2 Then
            Dim strFindLikeSql As String = ""
            If CInt(_ParamsValues.Item("当前英语备注ID")) > 0 Then
                strFindLikeSql = " WHERE MemoID=" & CInt(_ParamsValues.Item("当前英语备注ID"))
            End If
            sb.AppendLine(" AND WORD in(select EnglishWord from EnglishMEMO  " + strFindLikeSql + " )")
            '打印记录
        ElseIf ReportConst.EnglishTypeReport = 3 Then
            sb.AppendLine(" AND ID in( " + CStr(_ParamsValues.Item("编号列表")) + " )")
        End If

        '是否按浏览次数排序
        If ReportConst.EnglishOrderBy = 0 Then
            sb.AppendLine(" order by Count ")
            sb.AppendLine(" , OPTIME desc ")
        ElseIf ReportConst.EnglishOrderBy = 1 Then
            sb.AppendLine(" order by Count desc ")
            sb.AppendLine(" , OPTIME desc ")
        Else
            sb.AppendLine(" order by OPTIME desc ")
        End If

        Dim rs As ADODB.Recordset = Nothing
        rs = ct.GetTable(sb.ToString())
        '填充表
        oa.Fill(dt, rs)
        If dt Is Nothing Then
            Return New DataTable
        End If

        If dt.Rows.Count = 0 Then
            Return dt
        End If
        rs.Close()
        rs = Nothing

        If ReportConst.EnglishTypeReport = 3 Then
            Dim _dt内容 As DataTable = dt.Clone
            For Each strID As String In CStr(_ParamsValues.Item("编号列表")).Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)
                Dim drs() As DataRow = dt.Select("id=" & strID)
                If drs.Length > 0 Then
                    _dt内容.ImportRow(drs(0))
                End If
            Next

            If _dt内容.Rows.Count > 0 Then
                dt = _dt内容
            End If
        End If

        If CInt(_ParamsValues.Item("RowCount")) > 0 Then
            If CInt(_ParamsValues.Item("RowCount")) < dt.Rows.Count Then
                Dim dt1 As DataTable = dt.Clone
                For i As Integer = 0 To CInt(_ParamsValues.Item("RowCount")) - 1
                    dt1.ImportRow(dt.Rows(i))
                Next
                dt1.TableName = "XSCENLISHWORD"
                ds.Tables.Add(dt1)
            Else
                ds.Tables.Add(dt)
            End If
        Else
            ds.Tables.Add(dt)
        End If

        '添加到DS中    
        ds.Tables.Item(0).TableName = "XSCENLISHWORD"
        Dim contents() As String
        For index As Integer = 0 To ds.Tables(0).Rows.Count - 1
            contents = CStr(ds.Tables(0).Rows(index)("CHINESEWORD")).Split(New String() {vbNewLine}, StringSplitOptions.RemoveEmptyEntries)
            If contents.Length > 1 Then
                ds.Tables(0).Rows(index)("CHINESEWORD") = contents(0).PadRight(17).Substring(0, 17).Trim
            Else
                ds.Tables(0).Rows(index)("CHINESEWORD") = CStr(ds.Tables(0).Rows(index)("CHINESEWORD")).Trim.PadRight(17).Substring(0, 17).Trim
            End If
        Next
        Return ds.Tables(0)

    End Function

    Private Sub PrintProcess(ByVal content As String)
        lbl1000.Text = content
        System.Windows.Forms.Application.DoEvents()
    End Sub

    Private Sub PageComplete(ByVal page As Integer, ByVal totalPage As Integer)
        If page < totalPage Then
            lbl1000.Text = "正在打印" & page & "页 共" & totalPage & "页"
        Else
            lbl1000.Text = "打印结束"
        End If
        System.Windows.Forms.Application.DoEvents()
    End Sub
#End Region

#End Region

#Region "报表必须使用"
    Private _报表字体 As String = "宋体"
    Public Overrides Property 报表字体() As String
        Get
            Return _报表字体
        End Get
        Set(ByVal value As String)
            _报表字体 = value
        End Set
    End Property

    Private _报表记录条数字 As Integer = 39
    Public Overrides Property 报表记录条数字() As Integer
        Get
            Return _报表记录条数字
        End Get
        Set(ByVal value As Integer)
            _报表记录条数字 = value
        End Set
    End Property
#End Region

    Private Sub 保存数据(ByVal dataReport As DataTable)
        Try
            Dim bFirst As Boolean = False
            Dim sb As New StringBuilder
            For i As Int32 = 0 To dataReport.Rows.Count - 1
                If bFirst = False Then
                    sb.Append("'" & CStr(dataReport.Rows(i)("id")) & "'")
                    bFirst = True
                Else
                    sb.Append("," & "'" & CStr(dataReport.Rows(i)("id")) & "'")
                End If
            Next
            Dim strSql As String = "insert into PrintLog([type],[DateTime],[KEYS],id) values(0,?,?,?)"
            Dim cmd As New ADODB.Command
            cmd.CommandText = strSql
            Dim pa As ADODB.Parameter = cmd.CreateParameter(, ADODB.DataTypeEnum.adDBTimeStamp, ADODB.ParameterDirectionEnum.adParamInput, 100, Now.ToString())
            cmd.Parameters.Append(pa)

            pa = cmd.CreateParameter("KEYS", ADODB.DataTypeEnum.adVarWChar, ADODB.ParameterDirectionEnum.adParamInput, sb.ToString().Length, sb.ToString())
            cmd.Parameters.Append(pa)

            pa = cmd.CreateParameter(, ADODB.DataTypeEnum.adChar, ADODB.ParameterDirectionEnum.adParamInput, 36, System.Guid.NewGuid.ToString("D"))
            cmd.Parameters.Append(pa)

            If (ct.ExcCommand(cmd)) Then
                ShowMessageBox("保存成功")
            End If
        Catch ex As Exception

        End Try
    End Sub

End Class
